<?php


class DB
{
    public $host;
    public $user;
    public $password;
    public $dbName;
    public $port;
    public $mysqli_obj;
    public $where;
    public $orderBy;
    public $limit;
    public $result_obj;

    /**
     * DB constructor.
     * @param string $host
     * @param string $user
     * @param string $password
     * @param string $dbName
     * @param int $port
     */
    public function __construct($host = "127.0.0.1", $user = "root", $password = "root", $dbName = "message", $port = 3306)
    {
        $this->host = $host;
        $this->user = $user;
        $this->password = $password;
        $this->dbName = $dbName;
        $this->port = $port;
        $this->connectDB();
    }

    /**
     * 连接数据库
     */
    public function connectDB()
    {
        //   1. -----------连接数据库   抑制符---------
        $this->mysqli_obj = @new Mysqli($this->host, $this->user, $this->password, $this->dbName,$this->port);// @ 抑制错误提示

        //   2.  -----------判断数据库是否链接成功----------
        if ($this->mysqli_obj->connect_errno) {
            exit("数据库连接失败，失败原因：" . $this->mysqli_obj->connect_error);
        }

        //   3.   ----------设置字符集----------
         $charResult = $this->mysqli_obj->set_charset("utf8");
        //   4.   ------判断 字符集是否设置成功--------
        if (!$charResult) {
            exit("字符集设置失败,失败原因：" . $this->mysqli_obj->error);
        }
    }

    /**
     * 查询
     * @param $tableName
     * @return array
     */
    public function query($tableName,array $whereData = [])
    {
        $this->connectExists();

        foreach ($whereData as $key => $value) {
            $this->where($key,$value);
        }
        $sql = sprintf("select * from `%s` %s %s %s", $tableName, $this->where, $this->orderBy,$this->limit);

        $this->orderBy = "";
        $this->where = "";
        $this->limit = "";
        return $this->exeSql($sql);
    }

    /**
     * 增加
     * @param $tableName
     * @param array $data
     * @return array
     */
    public function insert($tableName,array $data)
    {
        $this->connectExists();
        $sql = $this->getInsertSql($tableName, $data);
        return $this->dealResult($sql,"增加");
    }

    /**
     * 判断是否执行成功
     * @param $sql
     * @param $msg
     * @return array
     */
    public function dealResult($sql, $msg)
    {
        $result = $this->mysqli_obj->query($sql);
        $insert_id = mysqli_insert_id($this->mysqli_obj);

        if($result && $this->mysqli_obj->affected_rows > 0) {
            return ["code" => 1, "msg" => "{$msg}成功", "new_id" => $insert_id];
        } else {
            return ["code" => 2, "msg" => "{$msg}失败，内容如下：" . $this->mysqli_obj->error, "error_sql" => $sql];
        }
    }

    /**
     * 执行sql
     * @param $sql
     * @return array
     */
    public function exeSql($sql)
    {
        $prefix_str = substr($sql,0,6);
        if ($prefix_str === "select") {
            $this->result_obj = $this->mysqli_obj->query($sql);
            if ( $this->mysqli_obj->errno ) {
                return ["code" => 2, "msg" => "数据库查询失败，原因" . $this -> mysqli_obj -> error];
            } else {
                $data = $this->result_obj ->fetch_all(1);
                $this ->result_obj ->free_result();
                return $data;
            }
        } else {
            return $this->dealResult($sql,"操作");
        }
    }

    /**拼接插入sql
     * @param $tableName
     * @param $data
     * @return string
     */
    public function getInsertSql($tableName, $data)
    {
        $fielStr = "";
        $varusStr = "";
        foreach ($data as $key => $value) {
            $fielStr .= "`{$key}`,";
            $varusStr .= "'{$value}',";
        }

        $fielStr = substr($fielStr,0,-1);
        $varusStr = substr($varusStr,0,-1);
        return sprintf("insert into `%s`(%s) values(%s)",$tableName,$fielStr,$varusStr);
    }

    /**
     * 删除
     * @param $tableName
     * @param array $whereData
     * @return array
     */
    public function delete($tableName,array $whereData = [])
    {
        $this->connectExists();

        foreach ($whereData as $key => $value) {
            $this->where($key,$value);
        }
        if (empty($this->where)) {
            return ["code" => 403, "msg" => "where条件失效，不允许执行该sql"];
        }
        $sql = sprintf("delete from `%s` %s", $tableName, $this->where);
        $this->where = "";
        return $this->dealResult($sql,"删除");
    }



    public function in ($field,array $data) {
        $value = "('". implode("','",$data) . "')";
        if (empty($this->where)) {
            $this->where .= " where `{$field}` IN {$value}";
        } else {
            $this->where .= " and `{$field}` IN '{$value}'";
        }
    }


    /**
     * where 的判断
     * @param $key
     * @param $value
     * @param string $sign
     */
    public function where($key,$value,$sign = "=")
    {
        if (!in_array($sign,["=" , ">" , "<" , ">=" , "<=" ,"!=" , "<>"])) exit("非法符号,请重试");
        if (empty($this->where)) {
            $this->where .= " where `{$key}` {$sign} '{$value}' ";
        } else {
            $this->where .= " and `{$key}` {$sign} '{$value}' ";
        }
    }

    /**
     * or
     * @param $key
     * @param $value
     * @param string $sign
     */
    public function ors($key,$value,$sign = "=")
    {
        if (!in_array($sign,["=" , ">" , "<" , ">=" , "<=" ,"!=" , "<>"])) exit("非法符号,请重试");
        if (empty($this->where)) {
            $this->where .= " where `{$key}` {$sign} '{$value}' ";
        } else {
            $this->where .= " or `{$key}` {$sign} '{$value}' ";
        }
    }

    /**
     * 排序
     * @param $field
     * @param string $order
     * @return string
     */
    public function orderBy($field,$order = "ASC")
    {
        if (!in_array($order,["ASC" , "DESC" ])) return "非法参数";
        if (empty($this->orderBy)) {
            $this->orderBy .= " ORDER BY `{$field}` {$order}  ";
        } else {
            $this->orderBy .= " , `{$field}` {$order} ";
        }
    }

    public function limit($start,$end = "")
    {
        if (empty($end)) {
            $this->limit .= "LIMIT {$start}";
        } else {
            $this->limit .= "LIMIT {$start} , {$end}";
        }
    }

    public function update($tableName,array $data,array $whereData = [])
    {
        $this->connectExists();
        $sql = $this->getUpdateSql($tableName, $data ,$whereData);
        //   6.  -----------执行sql---------
        $result = $this->mysqli_obj->query($sql);
        $this->where = "";//使用后将其变为空
        //  7.2  增删改的结果   $result;  bool 单独只用这个布尔值来判断
        if ($result && $this->mysqli_obj->affected_rows > 0) {
            return ["code" => 1,"msg" => "修改成功","data" => $data];   //
        } else {
            return ["code" => 2,"msg" => "修改失败, 内容如下：" . $this->mysqli_obj->error,"error_sql" => $sql];
        }
    }
    public function getUpdateSql($tableName, $data ,$whereData)
    {
        foreach ($whereData as $key => $value) {
            $this->where($key,$value);
        }

        if (empty($this->where)) {
            return ["code" => 403, "msg" => "where条件失效，不允许执行该sql"];
        }

        $fielStr = "";
        foreach ($data as $key => $value) {
            $fielStr .= "`{$key}` = '{$value}' ,";
        }
        //$fielStr = substr($fielStr,0,-36);
        $fielStr = substr($fielStr,0,-1);
        return sprintf("update `%s` set %s  %s", $tableName,$fielStr,$this->where);
    }

    /**
     * 判断数据库对象是否存在(判断连接是否成功)
     */
    public function connectExists()
    {
        if (empty($this->mysqli_obj)) $this->connectDB();
    }

    /**
     * 关闭连接
     */
    public function __destruct()
    {
        $this->mysqli_obj->close();
    }
}
//$db_obj = new DB();
//var_dump($db_obj);
//$datas = ["name" => "滴","sex" => 1,"age" => 20];
//$resu = $db_obj->update("student",$datas,["id" => 41]);
//var_dump($resu);


//$resu = $db_obj->delete("student",["id" => 42]);
//var_dump($resu);

//$datas = ["name" => "滴滴","sex" => 2,"age" => 20];
//$resu = $db_obj->insert("student",$datas);
//var_dump($resu);


//$resu = $db_obj->query("test");
//var_dump($resu);

//$resu = $db_obj->connectDB();
//var_dump($resu);












